/*
 * Copyright (c) 2018 - 2028. www.vtuzx.com Inc. All Rights Reserved
 */
package com.vtuzx.kingdee.xqc_api.service.impl;

import com.vtuzx.core.VtuzxException;
import com.vtuzx.core.bean.dto.VtuzxMap;
import com.vtuzx.core.bean.dto.VtuzxPage;
import com.vtuzx.core.constant.IVtuzxConst;
import com.vtuzx.core.file.excel.VtuzxExcel;
import com.vtuzx.core.file.excel.VtuzxExcelCell;
import com.vtuzx.core.file.excel.VtuzxExcelRow;
import com.vtuzx.core.file.excel.VtuzxExcelSheet;
import com.vtuzx.core.utils.VtuzxUtil;
import com.vtuzx.fast.utils.FastUtil;
import com.vtuzx.kingdee.xqc_api.service.IXqcAllocationService;
import com.vtuzx.kingdee.xqc_api.service.IXqcInventoryService;
import com.vtuzx.kingdee.xqc_api.service.IXqcTrayService;
import com.vtuzx.kingdee.xqc_core.bean.VtuzxArrayList;
import com.vtuzx.kingdee.xqc_core.bean.xqc.BillMaterial;
import com.vtuzx.kingdee.xqc_core.bean.xqc.BillMaterialBox;
import com.vtuzx.kingdee.xqc_core.constant.*;
import com.vtuzx.kingdee.xqc_core.dao.*;
import com.vtuzx.web.bean.VtuzxDownloadBean;
import org.bson.Document;
import org.bson.types.ObjectId;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.stereotype.Service;

import javax.print.Doc;
import java.io.ByteArrayOutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
public class XqcTrayServiceImpl implements IXqcTrayService {

    /**
     * Logger
     */
    private static final Logger _logger = LoggerFactory.getLogger(XqcTrayServiceImpl.class);

    @Autowired
    private MaterialDao materialDao;

    @Autowired
    private InventoryDao inventoryDao;

    @Autowired
    private InventoryDetailDao inventoryDetailDao;

    @Autowired
    private PurColDetailDao purColDetailDao;

    @Autowired
    private OtherColDetailDao otherColDetailDao;

    @Autowired
    private PrdColDetailDao prdColDetailDao;

    @Autowired
    private PurColDao purColDao;

    @Autowired
    private OtherColDao otherColDao;

    @Autowired
    private PrdColDao prdColDao;

    @Autowired
    private TrayDao trayDao;

    @Autowired
    private TrayBindDao trayBindDao;

    @Autowired
    private TrayBindDetailDao trayBindDetailDao;

    @Autowired
    private TrayBindDetailBoxDao trayBindDetailBoxDao;

    @Autowired
    private MDictDao dictDao;

    @Autowired
    private StockDao stockDao;

    @Autowired
    private TrayInventoryDao trayInventoryDao;

    @Autowired
    private TrayInventoryDetailDao trayInventoryDetailDao;


    @Override
    public VtuzxMap getTrayList(ObjectId userId, String name, Map<String, Object> param) {
        List<VtuzxMap> dataList = new ArrayList<>();
        Iterator<Document> trayItr = trayDao.findList(null, null, 0, 0);
        while (trayItr.hasNext()) {
            Document trayDoc = trayItr.next();
            VtuzxMap trayMap = new VtuzxMap(trayDoc);
            dataList.add(trayMap);
        }
        return new VtuzxMap(IVtuzxConst.Key_data, dataList);
    }

    @Override
    public VtuzxMap getMaterialBoxNoTray(ObjectId userId, String name, Map<String, Object> param) throws VtuzxException {
        String boxNoKey = VtuzxUtil.getString(param, "boxNo");

        // 管理箱号
        String boxNo;
        if (boxNoKey.contains("|;")) {
            String[] boxValues = boxNoKey.split("\\|\\;");
            if (boxValues.length == 6) {
                boxNo = boxNoKey;
                boxNo = boxNo.substring(boxNo.lastIndexOf("|;") + 2);
            } else {
                boxNo = boxValues[boxValues.length - 1];
            }
        } else {
            boxNo = boxNoKey;
        }
        List<String> boxNoList = new ArrayList<>();
        boxNoList.add(boxNo);
        return scanGetMaterialTray(userId, name, boxNoList);
    }

    @Override
    public VtuzxMap getMaterialRfidTray(ObjectId userId, String name, Map<String, Object> param) throws VtuzxException {
        List<String> rfidList = VtuzxUtil.getObject(param, "rfid");
        return scanGetMaterialTray(userId, name, rfidList);
    }

    private VtuzxMap scanGetMaterialTray(ObjectId userId, String userName, List<String> keyList) throws VtuzxException {
        List<VtuzxMap> dataList = new ArrayList<>();
        String key = keyList.get(0);
        Criteria cond = new Criteria();
        Criteria colCond = new Criteria();
        if (key.startsWith("A0")) { //如果是箱号
            cond.and("boxNo").in(keyList).and("status").is("in");
            colCond.and("boxNo").in(keyList).and("inStock").is(false);
        } else { //如果是rfid
            cond.and("rfid").in(keyList).and("status").is("in");
            colCond.and("rfid").in(keyList).and("inStock").is(false);
        }

        List<VtuzxMap> inventoryDetailList = new ArrayList<>();
        List<ObjectId> ids = new ArrayList<>();
        Iterator<Document> inventoryDetailItr = inventoryDetailDao.findList(cond, null, 0, 0);
        while (inventoryDetailItr.hasNext()) {
            Document inventoryDetailDoc = inventoryDetailItr.next();
            VtuzxMap inventoryDetailMap = new VtuzxMap(inventoryDetailDoc);
            ObjectId inventoryId = VtuzxUtil.getObject(inventoryDetailMap, "_inventoryId");
            ids.add(inventoryId);
            inventoryDetailMap.append("inventoryId", VtuzxUtil.removeString(inventoryDetailMap, "_inventoryId"));
            inventoryDetailMap.append("id", VtuzxUtil.removeString(inventoryDetailMap, "_id"));
            inventoryDetailList.add(inventoryDetailMap);
        }


        VtuzxMap inventorys = new VtuzxMap();
        List<String> materialNoList = new ArrayList<>();
        Iterator<Document> inventoryItr = inventoryDao.findList(Criteria.where("_id").in(ids), null, 0, 0);
        while (inventoryItr.hasNext()) {
            Document inventoryDoc = inventoryItr.next();
            VtuzxMap inventoryMap = new VtuzxMap(inventoryDoc);
            inventoryMap.append("id", VtuzxUtil.removeString(inventoryMap, "_id"));
            String id = VtuzxUtil.getString(inventoryMap, "id");
            materialNoList.add(VtuzxUtil.getString(inventoryMap, "materialNo"));
            inventorys.append(id, inventoryMap);
        }

        VtuzxMap materialMap = new VtuzxMap();
        Iterator<Document> materialItr = materialDao.findList(Criteria.where("fNumber").in(materialNoList), null, 0, 0);
        while (materialItr.hasNext()) {
            Document materialDoc = materialItr.next();
            VtuzxMap mtrMap = new VtuzxMap(materialDoc);
            String fNumber = VtuzxUtil.getString(mtrMap, "fNumber");
            materialMap.append(fNumber, mtrMap);
        }


        for (VtuzxMap inventoryDetail: inventoryDetailList) {
            VtuzxMap data = new VtuzxMap();
            String id = VtuzxUtil.getString(inventoryDetail, "id");
            String stockNo = VtuzxUtil.getString(inventoryDetail, "stockNo");
            String locationNo = VtuzxUtil.getString(inventoryDetail, "locationNo");
            String boxNo = VtuzxUtil.getString(inventoryDetail, "boxNo");
            String rfid = VtuzxUtil.getString(inventoryDetail, "rfid");
            double count = VtuzxUtil.getDouble(inventoryDetail, "count");
            String inventoryId = VtuzxUtil.getString(inventoryDetail, "inventoryId");
            VtuzxMap inventory = VtuzxUtil.getObject(inventorys, inventoryId);
            String materialNo = "";
            String batchNo = "";
            if (!VtuzxUtil.isEmpty(inventory)) {
                materialNo = VtuzxUtil.getString(inventory, "materialNo");
                batchNo = VtuzxUtil.getString(inventory, "batchNo");
            }
            VtuzxMap material = VtuzxUtil.getObject(materialMap, materialNo);
            String materialName = "";
            String materialSpec = "";
            String unit = "";
            if (!VtuzxUtil.isEmpty(material)) {
                materialName = VtuzxUtil.getString(material, "fName");
                materialSpec = VtuzxUtil.getString(material, "fSpecification");
                unit = VtuzxUtil.getString(material, "fBaseUnitIdNumber");
            }

            data.append("stockNo", stockNo)
                    .append("locationNo", locationNo)
                    .append("boxNo", boxNo)
                    .append("rfid", rfid)
                    .append("count", count)
                    .append("id", id)
                    .append("inventoryId", inventoryId)
                    .append("batchNo", batchNo)
                    .append("materialNo", materialNo)
                    .append("materialName", materialName)
                    .append("materialSpec", materialSpec)
                    .append("unit", unit);

            dataList.add(data);
        }

        List<String> prdBillNoList = new ArrayList<>();
        List<VtuzxMap> prdBillList = new ArrayList<>();
        Iterator<Document> prdColDetailItr = prdColDetailDao.findList(colCond, null, 0, 0);
        while (prdColDetailItr.hasNext()) {
            Document prdColDetail = prdColDetailItr.next();
            VtuzxMap prdColDetailMap = new VtuzxMap(prdColDetail);
            prdColDetailMap.append("id", VtuzxUtil.removeString(prdColDetailMap, "_id"));
            String billNo = VtuzxUtil.getString(prdColDetailMap, "billNo");
            prdBillNoList.add(billNo);
            prdBillList.add(prdColDetailMap);
        }
        VtuzxMap prdMap = new VtuzxMap();
        Iterator<Document> prdColItr = prdColDao.findList(Criteria.where("billNo").in(prdBillNoList), null, 0, 0);
        while (prdColItr.hasNext()) {
            Document prdColDoc = prdColItr.next();
            VtuzxMap prdColMap = new VtuzxMap(prdColDoc);
            String billNo = VtuzxUtil.getString(prdColMap, "billNo");
            prdMap.append(billNo, prdColMap);
        }

        for (VtuzxMap prdBill: prdBillList) {
            arrBillInfo(prdBill, prdMap, dataList);
        }


        List<String> purBillNoList = new ArrayList<>();
        List<VtuzxMap> purBillList = new ArrayList<>();
        Iterator<Document> purColDetailItr = purColDetailDao.findList(colCond, null, 0, 0);
        while (purColDetailItr.hasNext()) {
            Document purColDetail = purColDetailItr.next();
            VtuzxMap purColDetailMap = new VtuzxMap(purColDetail);
            purColDetailMap.append("id", VtuzxUtil.removeString(purColDetailMap, "_id"));
            String billNo = VtuzxUtil.getString(purColDetailMap, "billNo");
            purBillNoList.add(billNo);
            purBillList.add(purColDetailMap);
        }
        VtuzxMap purMap = new VtuzxMap();
        Iterator<Document> purColItr = purColDao.findList(Criteria.where("billNo").in(purBillNoList), null, 0, 0);
        while (purColItr.hasNext()) {
            Document purColDoc = purColItr.next();
            VtuzxMap purColMap = new VtuzxMap(purColDoc);
            String billNo = VtuzxUtil.getString(purColMap, "billNo");
            purMap.append(billNo, purColMap);
        }

        for (VtuzxMap purBill: purBillList) {
            arrBillInfo(purBill, purMap, dataList);
        }

        List<String> otherBillNoList = new ArrayList<>();
        List<VtuzxMap> otherBillList = new ArrayList<>();
        Iterator<Document> otherColDetailItr = otherColDetailDao.findList(colCond, null, 0, 0);
        while (otherColDetailItr.hasNext()) {
            Document otherColDetail = otherColDetailItr.next();
            VtuzxMap otherColDetailMap = new VtuzxMap(otherColDetail);
            otherColDetailMap.append("id", VtuzxUtil.removeString(otherColDetailMap, "_id"));
            String billNo = VtuzxUtil.getString(otherColDetailMap, "billNo");
            otherBillNoList.add(billNo);
            otherBillList.add(otherColDetailMap);
        }
        VtuzxMap otherMap = new VtuzxMap();
        Iterator<Document> otherColItr = otherColDao.findList(Criteria.where("billNo").in(otherBillNoList), null, 0, 0);
        while (otherColItr.hasNext()) {
            Document otherColDoc = otherColItr.next();
            VtuzxMap otherColMap = new VtuzxMap(otherColDoc);
            String billNo = VtuzxUtil.getString(otherColMap, "billNo");
            otherMap.append(billNo, otherColMap);
        }


        for (VtuzxMap otherBill: otherBillList) {
            arrBillInfo(otherBill, otherMap, dataList);
        }

        if (dataList.size() == 0) {
            throw new VtuzxException("箱号不在库!");
        }

        return new VtuzxMap(IVtuzxConst.Key_data, dataList);
    }

    private void arrBillInfo(VtuzxMap colDetailMap, VtuzxMap colMap, List<VtuzxMap> dataList) {
        VtuzxMap data = new VtuzxMap();
        String billNo = VtuzxUtil.getString(colDetailMap, "billNo");
        double count = VtuzxUtil.getDouble(colDetailMap, "count");
        String boxNo = VtuzxUtil.getString(colDetailMap, "boxNo");
        String rfid = VtuzxUtil.getString(colDetailMap, "rfid");
        String id = VtuzxUtil.getString(colDetailMap, "id");
        VtuzxMap cols = VtuzxUtil.getObject(colMap, billNo);
        String materialNo = "";
        String materialName = "";
        String materialSpec = "";
        String unit = "";
        String batchNo = "";
        if (!VtuzxUtil.isEmpty(cols)) {
            materialNo = VtuzxUtil.getString(cols, "materialNo");
            materialName = VtuzxUtil.getString(cols, "materialName");
            materialSpec = VtuzxUtil.getString(cols, "materialSpec");
            unit = VtuzxUtil.getString(cols, "unit");
            batchNo = VtuzxUtil.getString(cols, "batchNo");
        }
        data.append("count", count)
                .append("boxNo", boxNo)
                .append("rfid", rfid)
                .append("billNo", billNo)
                .append("id", id)
                .append("materialNo", materialNo)
                .append("materialName", materialName)
                .append("materialSpec", materialSpec)
                .append("unit", unit)
                .append("batchNo", batchNo);

        dataList.add(data);
    }


    @Override
    public void addTrayBox(ObjectId userId, String name, Map<String, Object> param) throws VtuzxException {
        List<Map<String, Object>> boxList = VtuzxUtil.getObject(param, "boxList");
        for (Map<String, Object> box: boxList) {
            addUserTrayBoxInner(userId, name, box);
        }
    }

    private void addUserTrayBoxInner(ObjectId userId, String name, Map<String, Object> data) throws VtuzxException {
        String boxNo = VtuzxUtil.getString(data, "boxNo");
        String materialNo = VtuzxUtil.getString(data, "materialNo");
        String materialName = VtuzxUtil.getString(data, "materialName");
        String materialSpec = VtuzxUtil.getString(data, "materialSpec");
        String batchNo = VtuzxUtil.getString(data, "batchNo");
        String unit = VtuzxUtil.getString(data, "unit");
        double count = VtuzxUtil.getDouble(data, "count");
        String rfid = VtuzxUtil.getString(data, "rfid");

        Document boxMap = trayBindDetailBoxDao.findOne(Criteria.where("boxNo").is(boxNo).and("isTemp").is(true));
        if (!VtuzxUtil.isEmpty(boxMap)) {
            String createUserName = VtuzxUtil.getString(boxMap, "createUserName");
            throw new VtuzxException("箱号已存在，请勿重复添加！【扫码人员：" + createUserName + "】");
        }

        Criteria detailCond = new Criteria();
        detailCond.and("isTemp").is(true);
        detailCond.and("materialNo").is(materialNo);
        detailCond.and("batchNo").is(batchNo);
        Document detail = trayBindDetailDao.findOne(detailCond);
        Document updateVal = new Document();

        if (detail == null) {
            updateVal.append("isTemp", true);
            updateVal.append("materialNo", materialNo);
            updateVal.append("materialName", materialName);
            updateVal.append("materialSpec", materialSpec);
            updateVal.append("batchNo", batchNo);
            updateVal.append("unit", unit);
            updateVal.append("_createUserId", userId);
            updateVal.append("createUserName", name);
            updateVal.append("boxCount", 1);
            updateVal.append("count", count);
        } else {
            updateVal = new Document("$inc", new Document("boxCount", 1).append("count", count));
        }
        ObjectId _detailId;
        if (detail == null) {
            _detailId = trayBindDetailDao.insertOne(updateVal);
        } else {
            _detailId = VtuzxUtil.getObject(detail, IVtuzxConst.Key__id);
            trayBindDetailDao.updateByIdRedirect(_detailId, updateVal);
        }

        Document detailBox = new Document();
        detailBox.append("isTemp", true);
        detailBox.append("_detailId", _detailId);
        detailBox.append("materialNo", materialNo);
        detailBox.append("materialName", materialName);
        detailBox.append("materialSpec", materialSpec);
        detailBox.append("boxNo", boxNo);
        detailBox.append("count", count);
        detailBox.append("batchNo", batchNo);
        detailBox.append("_createUserId", userId);
        detailBox.append("createUserName", name);
        detailBox.append("unit", unit);
        detailBox.append("rfid", rfid);
        trayBindDetailBoxDao.insertOne(detailBox);
    }

    @Override
    public void createTrayBill(ObjectId userId, String name, Map<String, Object> param) throws VtuzxException {
        String trayNumber = VtuzxUtil.getString(param, "trayNumber");
        String billNo = IXqcConst.createBillNo(BillType.TPBD);

        //判断当前托盘是否在别的仓库绑定过，如果绑定的话不允许绑定其他仓库的箱号
        Document inventoryDoc = inventoryDetailDao.findOne(Criteria.where("trayNumber").is(trayNumber)
        .and("status").is("in"));

        List<String> boxList = new ArrayList<>();
        double sumCount = 0d;
        int sumBoxCount = 0;
        Iterator<Document> trayBindDetailBoxItr = trayBindDetailBoxDao.findList(Criteria.where("isTemp").is(true).and("_createUserId").is(userId), null, 0, 0);
        while (trayBindDetailBoxItr.hasNext()) {
            Document trayBindDetailBoxDoc = trayBindDetailBoxItr.next();
            String boxNo = VtuzxUtil.getString(trayBindDetailBoxDoc, "boxNo");
            double count = VtuzxUtil.getDouble(trayBindDetailBoxDoc, "count");
            sumBoxCount++;
            sumCount += count;
            boxList.add(boxNo);
        }
        if (!VtuzxUtil.isEmpty(inventoryDoc)){
            String stockNo = VtuzxUtil.getString(inventoryDoc,"stockNo");
            String locationNo = VtuzxUtil.getString(inventoryDoc,"locationNo");
            for (String boxNo : boxList){
                Document boxDoc = inventoryDetailDao.findOne(Criteria.where("boxNo").is(boxNo).and("status").is("in"));
                String boxStockNo = VtuzxUtil.getString(boxDoc,"stockNo");
                String boxLocationNo = VtuzxUtil.getString(boxDoc,"locationNo");
                //如果箱号的仓库和仓位与托盘的不一致的话不允许绑定
                if (!stockNo.equals(boxStockNo) && !locationNo.equals(boxLocationNo)){
                    throw new VtuzxException("当前托盘与箱号所在仓库不一致，不允许绑定");
                }
            }
        }


        trayBindDetailDao.updateManyRedirect(Criteria.where("isTemp").is(true).and("_createUserId").is(userId), new Document("$set", new Document("billNo", billNo)).append("$unset", new Document("isTemp", 1)));

        trayBindDetailBoxDao.updateManyRedirect(Criteria.where("isTemp").is(true).and("_createUserId").is(userId), new Document("$set", new Document("billNo", billNo)).append("$unset", new Document("isTemp", 1)));

        trayBindDao.insertBill(userId, name, new Document("billNo", billNo).append("boxCount", sumBoxCount).append("count", sumCount).append("status", BillStatus.Audit.toString()));

        //给箱子绑定托盘码
        Criteria cond = new Criteria("boxNo").in(boxList).and("inStock").is(false);
        inventoryDetailDao.updateMany(Criteria.where("boxNo").in(boxList).and("status").is("in"), new Document("trayNumber", trayNumber));
        purColDetailDao.updateMany(cond, new Document("trayNumber", trayNumber));
        prdColDetailDao.updateMany(cond, new Document("trayNumber", trayNumber));
        otherColDetailDao.updateMany(cond, new Document("trayNumber", trayNumber));

    }

    @Override
    public VtuzxMap getUserMaterialListTray(ObjectId userId, String name, Map<String, Object> param) {
        List<VtuzxMap> dataList = new ArrayList<>();
        Iterator<Document> trayBindDetailItr = trayBindDetailDao.findList(Criteria.where("isTemp").is(true).and("_createUserId").is(userId), null, 0, 0);
        while (trayBindDetailItr.hasNext()) {
            Document trayDetailDoc = trayBindDetailItr.next();
            VtuzxMap trayDetailMap = new VtuzxMap(trayDetailDoc);
            trayDetailMap.append("id", VtuzxUtil.removeString(trayDetailMap, "_id"));
            dataList.add(trayDetailMap);
        }
        return new VtuzxMap(IVtuzxConst.Key_data, dataList);
    }

    @Override
    public VtuzxMap getUserBoxListTray(ObjectId userId, String name, Map<String, Object> param) {
        String id = VtuzxUtil.getString(param, "id");
        List<VtuzxMap> dataList = new ArrayList<>();
        Iterator<Document> trayBindDetailBoxItr = trayBindDetailBoxDao.findList(Criteria.where("_detailId").is(FastUtil.convertId(id)), null, 0, 0);
        while (trayBindDetailBoxItr.hasNext()) {
            Document trayDetailBoxDoc = trayBindDetailBoxItr.next();
            VtuzxMap trayDetailBoxMap = new VtuzxMap(trayDetailBoxDoc);
            trayDetailBoxMap.append("id", VtuzxUtil.removeString(trayDetailBoxMap, "_id"));
            trayDetailBoxMap.append("detailId", VtuzxUtil.removeString(trayDetailBoxMap, "_detailId"));
            dataList.add(trayDetailBoxMap);
        }
        return new VtuzxMap(IVtuzxConst.Key_data, dataList);
    }

    @Override
    public void deleteUserMaterialTray(ObjectId userId, String name, Map<String, Object> param) {
        String id = VtuzxUtil.getString(param, "id");
        Document material = trayBindDetailDao.findById(id);
        String billNo = VtuzxUtil.getString(material, "billNo");
        trayBindDetailDao.deleteById(id);

        double count = 0d;
        int boxCount = 0;
        Iterator<Document> allocationDetailItr = trayBindDetailDao.findList(Criteria.where("billNo").is(billNo), null, 0, 0);
        while (allocationDetailItr.hasNext()) {
            Document detail = allocationDetailItr.next();
            int detailBoxCount = VtuzxUtil.getInt(detail, "boxCount");
            double detailCount = VtuzxUtil.getDouble(detail, "count");
            count += detailCount;
            boxCount += detailBoxCount;
        }

        trayBindDao.updateOne(Criteria.where("billNo").is(billNo), new Document("boxCount", boxCount).append("count", count));

        trayBindDetailBoxDao.deleteMany(Criteria.where("_detailId").is(new ObjectId(id)));

    }

    @Override
    public void deleteUserBoxTray(ObjectId userId, String name, Map<String, Object> param) {
        String id = VtuzxUtil.getString(param, "id");
        Document box = trayBindDetailBoxDao.findById(id);
        ObjectId _detailId = VtuzxUtil.getObject(box, "_detailId");
        trayBindDetailBoxDao.deleteById(id);
        double count = 0d;
        int boxCount = 0;
        Iterator<Document> boxItr = trayBindDetailBoxDao.findList(Criteria.where("_detailId").is(_detailId), null, 0, 0);
        while (boxItr.hasNext()) {
            Document boxDoc = boxItr.next();
            count += VtuzxUtil.getDouble(boxDoc, "count");
            boxCount ++;
        }
        if (boxCount == 0) {
            trayBindDetailDao.deleteById(_detailId);
        } else {
            trayBindDetailDao.updateById(_detailId, new Document("count", count).append("boxCount", boxCount));
        }

    }


    @Override
    public VtuzxMap getTrayBindList(ObjectId userId, String name, Map<String, Object> param) {
        String billNo = VtuzxUtil.getString(param, "billNo");
        String createUserName = VtuzxUtil.getString(param, "createUserName");
        List<String> createTime = VtuzxUtil.getObject(param, "createTime");
        int pageIndex = VtuzxUtil.getInt(param, "pageIndex");
        int pageSize = VtuzxUtil.getInt(param, "pageSize");
        Criteria cond = new Criteria();
        if (!VtuzxUtil.isEmpty(billNo)) {
            cond.and("billNo").regex(billNo);
        }
        if (!VtuzxUtil.isEmpty(createUserName)) {
            cond.and("createUserName").regex(createUserName);
        }
        if (!VtuzxUtil.isEmpty(createTime)) {
            cond.and("createTime").gte(VtuzxUtil.parseDate(createTime.get(0))).lte(VtuzxUtil.parseDate(createTime.get(1) + " 23:59:59"));
        }

        VtuzxPage page = trayBindDao.page(cond, new Document("createTime", -1), pageIndex, pageSize);
        List<VtuzxMap> dataList = page.iterator(doc -> new VtuzxMap("id", VtuzxUtil.getString(doc, IVtuzxConst.Key__id))
                .append("billNo", VtuzxUtil.getString(doc, "billNo"))
                .append("boxCount", VtuzxUtil.getInt(doc, "boxCount"))
                .append("count", VtuzxUtil.getDouble(doc, "count"))
                .append("status", VtuzxUtil.getString(doc, "status"))
                .append("_createUserId", VtuzxUtil.getString(doc, "_createUserId"))
                .append("createUserName", VtuzxUtil.getString(doc, "createUserName"))
                .append("createTime", VtuzxUtil.formatYmdHmsF(VtuzxUtil.getObject(doc, "createTime"))));
        return new VtuzxMap(IVtuzxConst.Key_total, page.total()).append(IVtuzxConst.Key_data, dataList);
    }

    @Override
    public VtuzxMap getTrayBindDetail(ObjectId userId, String name, Map<String, Object> param) {
        String billNo = VtuzxUtil.getString(param, "billNo");
        Document allocationDoc = trayBindDao.findOne(Criteria.where("billNo").is(billNo));
        VtuzxMap header = new VtuzxMap("billNo", billNo)
                .append("boxCount", VtuzxUtil.getInt(allocationDoc, "boxCount"))
                .append("count", VtuzxUtil.getDouble(allocationDoc, "count"))
                .append("createUserName", VtuzxUtil.getBoolean(allocationDoc, "createUserName"))
                .append("createTime", VtuzxUtil.formatYmdHmsF(VtuzxUtil.getObject(allocationDoc, "createTime")));

        List<VtuzxMap> dataList = new ArrayList<>();
        Iterator<Document> allocationDetailItr = trayBindDetailDao.findList(Criteria.where("billNo").is(billNo), new Document("status", -1).append("TPALCount", 1).append("createTime", -1), 0, 0);

        while (allocationDetailItr.hasNext()) {
            Document allocationDetailDoc = allocationDetailItr.next();
            VtuzxMap allocationDetails = new VtuzxMap(allocationDetailDoc);
            allocationDetails.remove(IVtuzxConst.Key_updateTime);
            allocationDetails.remove(IVtuzxConst.Key_createTime);
            String id = VtuzxUtil.removeString(allocationDetails, IVtuzxConst.Key__id);
            allocationDetails.append("id", id);
            dataList.add(allocationDetails);
        }

        return new VtuzxMap("header", header).append(IVtuzxConst.Key_data, dataList);
    }

    @Override
    public VtuzxMap getTrayBindDetailBox(ObjectId userId, String name, Map<String, Object> param) {
        String billNo = VtuzxUtil.getString(param, "billNo");
        List<VtuzxMap> dataList = new ArrayList<>();
        Iterator<Document> boxItr = trayBindDetailBoxDao.findList(Criteria.where("billNo").is(billNo), new Document("boxNo", 1), 0, 0);
        while (boxItr.hasNext()) {
            Document boxDoc = boxItr.next();
            VtuzxMap boxs = new VtuzxMap(boxDoc);
            boxs.remove(IVtuzxConst.Key_updateTime);
            boxs.remove(IVtuzxConst.Key_createTime);
            String boxId = VtuzxUtil.removeString(boxs, IVtuzxConst.Key__id);
            boxs.append("id", boxId);
            dataList.add(boxs);
        }
        return new VtuzxMap(IVtuzxConst.Key_data, dataList);
    }

    @Override
    public void bindTrayRfid(String boxNo, String rfid) throws VtuzxException {
        Iterator<Document> trayItr = trayDao.findList(Criteria.where("rfid").ne(null), null, 0, 0);
        while (trayItr.hasNext()) {
            Document trayDoc = trayItr.next();
            List<String> rList = VtuzxUtil.getObject(trayDoc, "rfid");
            if (rList.contains(rfid)) {
                throw new VtuzxException("rfid已绑定托盘，请勿重复绑定");
            }
        }

        Document boxDoc = trayDao.findOne(Criteria.where("trayNumber").is(boxNo));
        if (boxDoc == null) {
            throw new VtuzxException(IXqcConst.Msg_E_XQC_box_not_exist, boxNo);
        }
        ObjectId _id = VtuzxUtil.getObject(boxDoc, IVtuzxConst.Key__id);
        List<String> rfidList = new ArrayList<>();
        if (VtuzxUtil.getObject(boxDoc, "rfid") != null) {
            rfidList = VtuzxUtil.getObject(boxDoc, "rfid");
        }
        if (rfidList.size() >= 2) {
            rfidList.remove(0);
        }
        rfidList.add(rfid);
        trayDao.updateById(_id, new Document("rfid", rfidList));
    }

    @Override
    public VtuzxDownloadBean downloadTrayList(ObjectId userId, Map<String, Object> param) throws Exception {
        List<Map<String, Object>> trayList = VtuzxUtil.getObject(param, "trayList");
        String timestamp = new SimpleDateFormat("yyMMddHHmmss").format(new Date());

        List<VtuzxExcelSheet> sheets = new ArrayList<>();
        VtuzxExcelSheet sheet0 = new VtuzxExcelSheet(0, "托盘信息列表");
        int col = 0;
        int row = 0;
        VtuzxExcelRow header0 = new VtuzxExcelRow(row++, new VtuzxArrayList<>(
                new VtuzxExcelCell(col++, "托盘编号"),
                new VtuzxExcelCell(col++, "托盘名称"),
                new VtuzxExcelCell(col++, "RFID1"),
                new VtuzxExcelCell(col++, "RFID2"),
                new VtuzxExcelCell(col++, "创建人员"),
                new VtuzxExcelCell(col++, "创建时间"),
                new VtuzxExcelCell(col, "备注")
        ));
        sheet0.addRow(header0);

        for(Map<String, Object> tray: trayList) {
            String trayNumber = VtuzxUtil.getString(tray, "trayNumber");
            String trayName = VtuzxUtil.getString(tray, "trayName");
            List<String> rfid = VtuzxUtil.getObject(tray, "rfid");
            String rfid1 = "";
            String rfid2 = "";
            if (rfid != null) {
                rfid1 = rfid.get(0);
                if (rfid.size() == 2) {
                    rfid2 = rfid.get(1);
                }
            }
            String createUserName = VtuzxUtil.getString(tray, "createUserName");
            String createTime = VtuzxUtil.getString(tray, "createTime");
            String remark = VtuzxUtil.getString(tray, "remark");
            col = 0;
            VtuzxExcelRow dataRow = new VtuzxExcelRow(row++, new VtuzxArrayList<>(
                    new VtuzxExcelCell(col++, trayNumber),
                    new VtuzxExcelCell(col++, trayName),
                    new VtuzxExcelCell(col++, rfid1),
                    new VtuzxExcelCell(col++, rfid2),
                    new VtuzxExcelCell(col++, createUserName),
                    new VtuzxExcelCell(col++, createTime),
                    new VtuzxExcelCell(col, remark)
            ));
            sheet0.addRow(dataRow);

        }

        sheet0.setColumnWidth(new VtuzxArrayList<>(
                new int[] { 0, 200 },
                new int[] { 1, 200 },
                new int[] { 2, 250 },
                new int[] { 3, 250 },
                new int[] { 4, 150 },
                new int[] { 5, 250 },
                new int[] { 6, 300 }
        ));

        sheets.add(sheet0);
        ByteArrayOutputStream stream = new ByteArrayOutputStream();
        VtuzxExcel.write(stream, sheets);
        return new VtuzxDownloadBean("托盘信息" + timestamp + ".xlsx", stream);
    }

    @Override
    public VtuzxMap scanTray(ObjectId userId, String name, Map<String, Object> param) throws VtuzxException {
        Criteria cond = new Criteria();
        String key = VtuzxUtil.getString(param, "key");
        Document trayDoc = trayDao.findOne(cond.orOperator(Criteria.where("rfid").in(key), Criteria.where("trayNumber").is(key)));
        if (trayDoc == null) {
            throw new VtuzxException("托盘不存在");
        }

        return new VtuzxMap("tray", new Document(trayDoc));
    }

    @Override
    public VtuzxMap trayInventory(ObjectId userId, String name, Map<String, Object> param) {
        Date now = new Date();
        Date startTime = VtuzxUtil.calcDate(now, Calendar.HOUR_OF_DAY, -3);
        trayInventoryInner(userId, name, startTime);
        return null;
    }


    private void trayInventoryInner(ObjectId userId, String name, Date startTime) {
        String billNo = IXqcConst.createBillNo(BillType.TPPD);
        List<String> ipList = new ArrayList<>();
        List<Date> dateList = new ArrayList<>();
        Iterator<Document> trayItr = trayDao.trayGroup(Criteria.where("lastInventoryTime").gte(startTime));
        while (trayItr.hasNext()) {
            Document trayDoc = trayItr.next();
            Date lastDate = VtuzxUtil.getObject(trayDoc, "maxDate");
            Document doc = VtuzxUtil.getObject(trayDoc, "_id");
            String ip = VtuzxUtil.getString(doc, "ip");
            ipList.add(ip);
            dateList.add(lastDate);
        }


        VtuzxMap ipStockMap = new VtuzxMap();//key ip value 仓库编号
        Iterator<Document> stockItr = stockDao.findList(Criteria.where("ip").in(ipList), null, 0, 0);
        while (stockItr.hasNext()) {
            Document stockDoc = stockItr.next();
            List<String> ipStrList = VtuzxUtil.getObject(stockDoc, "ip");
            for (String ipStr: ipStrList) {
                ipStockMap.append(ipStr, VtuzxUtil.getString(stockDoc, "fNumber"));
            }
        }

        List<Document> realList = new ArrayList<>();
        Iterator<Document> trayDocItr = trayDao.findList(Criteria.where("lastInventoryTime").in(dateList), null, 0, 0);
        while (trayDocItr.hasNext()) {
            Document trayDoc = trayDocItr.next();
            realList.add(trayDoc);
        }

        List<Document> bookList = new ArrayList<>();
        //.and("trayNumber").ne(null)
        Iterator<Document> boxGroupItr = inventoryDetailDao.trayGroup(Criteria.where("status").is("in"));
        while (boxGroupItr.hasNext()) {
            Document boxGroup = boxGroupItr.next();
            bookList.add(boxGroup);
        }


        List<Document> dataList = new ArrayList<>();
        for (Document trayDoc: realList) {
            Document data = new Document();
            String ip = VtuzxUtil.getString(trayDoc, "ip");
            String trayNumber = VtuzxUtil.getString(trayDoc, "trayNumber");//托盘编号
            String stockNo = VtuzxUtil.getString(ipStockMap, ip);//根据ip查询仓库
            if (VtuzxUtil.isEmpty(stockNo)) {
                continue;
            }
            data.append("trayNumber", trayNumber);
            data.append("stockNo", stockNo);
            data.append("billNo", billNo);
            Document bookTray = getBookTray(bookList, trayNumber);
            if (VtuzxUtil.isEmpty(bookTray)) {
                //只有实际数（盘盈）
                //如果哪个仓库都没有，那就是真盘盈，如果在别的仓库有，就拿别的仓库的ip，去托盘里找最近三小时里存不存在这个托盘，如果存在这条就不要了
                data.append("status", "surplus");
            } else {
                Document doc = VtuzxUtil.getObject(bookTray, "_id");
                String stockNumber = VtuzxUtil.getString(doc, "stockNo");
                if (stockNumber.equals(stockNo)) {
                    //都有
                    data.append("status", "save");
                    data.append("locationNo", VtuzxUtil.getString(bookTray, "locationNo"));
                    data.append("count", VtuzxUtil.getDouble(bookTray, "sumCount"));
                    bookList.remove(bookTray);
                } else {
                    Document findReal = findReal(realList, stockNumber, trayNumber, ipStockMap);
                    //账面对应的仓库的托盘码在实际库存里存在，说明当次是无效数据
                    if (findReal != null) {
                        continue;
                    } else {
                        data.append("status", "surplus");
                    }

                }

            }
            dataList.add(data);

        }
        if (bookList.size() > 0) {
            for (Document book: bookList) {
                //只有账面数
                Document data = new Document();
                Document doc = VtuzxUtil.getObject(book, "_id");
                String bookStockNo = VtuzxUtil.getString(doc, "stockNo");
                String bookTrayNumber = VtuzxUtil.getString(doc, "trayNumber");
                String locationNo = VtuzxUtil.getString(book, "locationNo");
                data.append("stockNo", bookStockNo);
                data.append("trayNumber", bookTrayNumber);
                data.append("locationNo", locationNo);
                data.append("status", "loss");
                data.append("count", "-" + VtuzxUtil.getDouble(book, "sumCount"));
                data.append("billNo", billNo);
                dataList.add(data);
            }
        }

        trayInventoryDao.insertBill(userId, name, new Document("billNo", billNo).append("status", BillStatus.Audit.toString()));
        trayInventoryDetailDao.insertMany(dataList);


    }

    private Document findReal(List<Document> realList, String stockNumber, String trayNumber, VtuzxMap ipStockMap) {
        for (Document real: realList) {
            String ip = VtuzxUtil.getString(real, "ip");
            String stockNo = VtuzxUtil.getString(ipStockMap, ip);
            String trayNo = VtuzxUtil.getString(real, "trayNumber");
            if (stockNumber.equals(stockNo) && trayNo.equals(trayNumber)) {
                return real;
            }
        }
        return null;
    }

    private Document getBookTray(List<Document> bookList, String trayNumber) {
        for (Document book: bookList) {
            Document doc = VtuzxUtil.getObject(book, "_id");
            String bookTrayNumber = VtuzxUtil.getString(doc, "trayNumber");
            if (bookTrayNumber.equals(trayNumber)) {
                return book;
            }
        }
        return null;
    }


    @Override
    public VtuzxMap getTrayInventoryList(ObjectId userId, String name, Map<String, Object> param) {
        String billNo = VtuzxUtil.getString(param, "billNo");
        List<String> createTime = VtuzxUtil.getObject(param, "createTime");
        int pageSize = VtuzxUtil.getInt(param, "pageSize");
        int pageIndex = VtuzxUtil.getInt(param, "pageIndex");
        Criteria cond = new Criteria();
        if (!VtuzxUtil.isEmpty(billNo)) {
            cond.and("billNo").regex(billNo);
        }
        if (!VtuzxUtil.isEmpty(createTime)) {
            cond.and("createTime").gte(VtuzxUtil.parseDate(createTime.get(0))).lte(VtuzxUtil.parseDate(createTime.get(1) + " 23:59:59"));
        }
        VtuzxPage page = trayInventoryDao.page(cond, new Document("createTime", -1), pageIndex, pageSize);
        List<VtuzxMap> dataList = page.iterator(doc -> new VtuzxMap("id", VtuzxUtil.getString(doc, IVtuzxConst.Key__id))
                .append("billNo", VtuzxUtil.getString(doc, "billNo"))
                .append("status", VtuzxUtil.getString(doc, "status"))
                .append("_createUserId", VtuzxUtil.getString(doc, "_createUserId"))
                .append("createUserName", VtuzxUtil.getString(doc, "createUserName"))
                .append("createTime", VtuzxUtil.formatYmdHmsF(VtuzxUtil.getObject(doc, "createTime"))));
        return new VtuzxMap(IVtuzxConst.Key_total, page.total()).append(IVtuzxConst.Key_data, dataList);
    }

    @Override
    public VtuzxMap getTrayInventoryDetail(ObjectId userId, String name, Map<String, Object> param) {
        List<VtuzxMap> dataList = new ArrayList<>();
        String billNo = VtuzxUtil.getString(param, "billNo");

        Document trayInventoryDoc = trayInventoryDao.findOne(Criteria.where("billNo").is(billNo));
        VtuzxMap header = new VtuzxMap(trayInventoryDoc);
        header.append("id", VtuzxUtil.removeString(header, IVtuzxConst.Key__id));

        Iterator<Document> trayInventoryDetailItr = trayInventoryDetailDao.findList(Criteria.where("billNo").is(billNo), null, 0, 0);
        while (trayInventoryDetailItr.hasNext()) {
            Document trayInventoryDetailDoc = trayInventoryDetailItr.next();
            VtuzxMap trayInventoryDetailMap = new VtuzxMap(trayInventoryDetailDoc);
            trayInventoryDetailMap.append("id", VtuzxUtil.removeString(trayInventoryDetailMap, IVtuzxConst.Key__id));
            trayInventoryDetailMap.append("unitName", VtuzxUtil.removeString(trayInventoryDetailMap, "unit"));
            dataList.add(trayInventoryDetailMap);
        }
        return new VtuzxMap(IVtuzxConst.Key_data, dataList).append("header", header);
    }

    @Override
    public VtuzxMap downloadTrayInventoryList(ObjectId userId, Map<String, Object> param) throws Exception {
        String trayNumber = VtuzxUtil.getString(param, "trayNumber");
        String stockNo = VtuzxUtil.getString(param, "stockNo");
        String billNo = VtuzxUtil.getString(param, "billNo");
        List<VtuzxMap> detailList = new ArrayList<>();
        List<String> stockNoList = new ArrayList<>();
        Iterator<Document> detailItr = trayInventoryDetailDao.findList(Criteria.where("billNo").is(billNo), null, 0, 0);
        while (detailItr.hasNext()) {
            Document detailDoc = detailItr.next();
            VtuzxMap detailMap = new VtuzxMap(detailDoc);
            Date createTime = VtuzxUtil.getObject(detailMap, "createTime");
            String stock = VtuzxUtil.getString(detailMap, "stockNo");
            detailMap.remove("createTime");
            detailMap.append("createTime", VtuzxUtil.formatYmdHmsF(createTime));
            detailList.add(detailMap);
            stockNoList.add(stock);
        }

        VtuzxMap stockNoMap = new VtuzxMap();
        Iterator<Document> stockItr = stockDao.findList(Criteria.where("fNumber").in(stockNoList), null, 0, 0);
        while (stockItr.hasNext()) {
            Document stockDoc = stockItr.next();
            String stockNumber = VtuzxUtil.getString(stockDoc, "fNumber");
            String stockName = VtuzxUtil.getString(stockDoc, "fName");
            stockNoMap.append(stockNumber, stockName);
        }

        for (VtuzxMap detail: detailList) {
            String status = VtuzxUtil.getString(detail, "status");
            if (status.equals(TrayInventoryStatus.Loss.toString())) {
                detail.append("statusName", TrayInventoryStatus.LossName.toString());
            } else if (status.equals(TrayInventoryStatus.Surplus.toString())) {
                detail.append("statusName", TrayInventoryStatus.SurplusName.toString());
            } else if (status.equals(TrayInventoryStatus.Save.toString())) {
                detail.append("statusName", TrayInventoryStatus.SaveName.toString());
            }
            String stockNumber = VtuzxUtil.getString(detail, "stockNo");
            String stockName = VtuzxUtil.getString(stockNoMap, stockNumber);
            if (stockName == null) {
                stockName = "";
            }
            detail.append("stockName", stockName);
        }

        String timestamp = new SimpleDateFormat("yyMMddHHmmss").format(new Date());

        List<VtuzxExcelSheet> sheets = new ArrayList<>();
        VtuzxExcelSheet sheet0 = new VtuzxExcelSheet(0, "托盘盘点");
        int col = 0;
        int row = 0;
        VtuzxExcelRow header0 = new VtuzxExcelRow(row++, new VtuzxArrayList<>(
                new VtuzxExcelCell(col++, "仓库"),
                new VtuzxExcelCell(col++, "托盘编码"),
                new VtuzxExcelCell(col++, "仓位"),
                new VtuzxExcelCell(col++, "数量"),
                new VtuzxExcelCell(col, "状态")
        ));
        sheet0.addRow(header0);


        for (VtuzxMap detail : detailList) {
            col = 0;
            VtuzxExcelRow dataRow = new VtuzxExcelRow(row++, new VtuzxArrayList<>(
                    new VtuzxExcelCell(col++, VtuzxUtil.getString(detail, "stockName")),
                    new VtuzxExcelCell(col++, VtuzxUtil.getString(detail, "trayNumber")),
                    new VtuzxExcelCell(col++, VtuzxUtil.getString(detail, "locationNo")),
                    new VtuzxExcelCell(col++, VtuzxUtil.getString(detail, "count")),
                    new VtuzxExcelCell(col, VtuzxUtil.getString(detail, "statusName"))
            ));
            sheet0.addRow(dataRow);
        }

        sheet0.setColumnWidth(new VtuzxArrayList<>(
                new int[]{0, 200},
                new int[]{1, 300},
                new int[]{2, 200},
                new int[]{3, 200},
                new int[]{4, 200}
        ));

        sheets.add(sheet0);
        ByteArrayOutputStream stream = new ByteArrayOutputStream();
        VtuzxExcel.write(stream, sheets);
        return new VtuzxDownloadBean("托盘盘点" + billNo + ".xlsx", stream);
    }
}
